﻿//using CosenSecurity;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Easy.Model;

namespace Easy.Operation
{
    #region Sql语句转换

    public class SqlConvert
    {
        public static string ToRetrieveCommand(string entityName, Dictionary<string, Field> fields, Guid id, string[] columns)
        {
            string select = "t.*";
            if (columns != null)
                select = "t." + string.Join(",t.", columns);


            int i = 0;
            StringBuilder sbJoins = new StringBuilder();
            StringBuilder sbSelect = new StringBuilder();
            foreach (var item in fields)
            {
                if (item.Value.Type == FieldType.lookup)
                {
                    i++;
                    sbJoins.Append(string.Format(@"----关联实体引用
                                                LEFT JOIN {0} j{1} WITH(NOLOCK) ON t.{2}=j{1}.{0}Id ", item.Value.LookupEntityName,
                                                                                                  i.ToString(),
                                                                                                  item.Value.Name));
                    string lookupPrimaryAtt = "";
                    lookupPrimaryAtt = item.Value.LookupPrimaryAtt;
                    sbSelect.Append(string.Format(@",j{0}.{1} AS {2}Name ", i.ToString(), lookupPrimaryAtt, item.Value.Name));
                }
            }
            select += sbSelect.ToString();
            string commandText = string.Format(@"--查询主键记录
                                                SELECT  {0}
                                                FROM    {1} t WITH(NOLOCK) 
                                                {2}
                                                WHERE   t.{1}Id = '{3}'", select, entityName, sbJoins.ToString(), id.ToString());


            return commandText;
        }

        public static string ToCreateCommand(string entityName, Dictionary<string, object> values, Dictionary<string, Field> fields)
        {

            StringBuilder sbColumn = new StringBuilder();
            StringBuilder sbValue = new StringBuilder();
            foreach (var item in values)
            {

                sbColumn.Append(string.Format(" {0},", item.Key));

                switch (fields[item.Key].Type)
                {
                    case FieldType.key:
                    case FieldType.nvarchar:
                    case FieldType.ntext:
                    case FieldType.image:
                        {
                            if (item.Value != null)
                                sbValue.Append(string.Format(" '{0}',", item.Value.ToString().Replace("'", "''")));
                            else
                                sbValue.Append("null,");
                            break;
                        }
                    case FieldType.datetime:
                        {
                            if (item.Value != null)
                                sbValue.Append(string.Format(" '{0}',", ((DateTime)item.Value).ToString("yyyy-MM-dd HH:mm:ss")));
                            else
                                sbValue.Append("null,");
                            break;
                        }
                    case FieldType.date:
                        {
                            if (item.Value != null)
                                sbValue.Append(string.Format(" '{0}',", ((DateTime)item.Value).ToString("yyyy-MM-dd")));
                            else
                                sbValue.Append("null,");
                            break;
                        }
                    case FieldType.picklist:
                    case FieldType.@int:
                    case FieldType.bit:
                    case FieldType.@float:
                        {
                            if (item.Value != null)
                                sbValue.Append(string.Format(" {0},", item.Value.ToString()));
                            else
                                sbValue.Append("null,");
                            break;
                        }
                    case FieldType.lookup:
                        {
                            if (item.Value != null && ((EntityReference)item.Value).id.ToString() != "")
                                sbValue.Append(string.Format(" '{0}',", ((EntityReference)item.Value).id.ToString()));
                            else
                                sbValue.Append("null,");
                            break;
                        }
                    case FieldType.password:
                        {
                            if (item.Value != null)
                                sbValue.Append(string.Format(" '{0}',", System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(item.Value.ToString(), "MD5")));
                            else
                                sbValue.Append("null,");
                            break;
                        }

                }

            }


            string commandText = string.Format(@"--插入记录
                                                INSERT INTO {0}  ({1}) VALUES ({2})", entityName, sbColumn.ToString().TrimEnd(','), sbValue.ToString().TrimEnd(','));
            return commandText;
        }

        public static string ToUpdateCommnad(string entityName, Dictionary<string, object> values, Dictionary<string, Field> fields)
        {

            StringBuilder sbValue = new StringBuilder();
            foreach (var item in values)
            {
                switch (fields[item.Key].Type)
                {
                    //case FieldType.Key:
                    case FieldType.nvarchar:
                    case FieldType.ntext:
                    case FieldType.image:
                        {
                            if (item.Value != null)
                                sbValue.Append(string.Format(" {0}='{1}',", item.Key, item.Value.ToString().Replace("'", "''")));
                            else
                                sbValue.Append(string.Format(" {0}=null, ", item.Key));
                            break;
                        }
                    case FieldType.datetime:
                        {
                            if (item.Value != null)
                                sbValue.Append(string.Format(" {0}='{1}',", item.Key, ((DateTime)item.Value).ToString("yyyy-MM-dd HH:mm:ss")));
                            else
                                sbValue.Append(string.Format(" {0}=null, ", item.Key));
                            break;
                        }
                    case FieldType.date:
                        {
                            if (item.Value != null)
                                sbValue.Append(string.Format(" {0}='{1}',", item.Key, ((DateTime)item.Value).ToString("yyyy-MM-dd")));
                            else
                                sbValue.Append(string.Format(" {0}=null, ", item.Key));
                            break;
                        }
                    case FieldType.picklist:
                    case FieldType.@int:
                    case FieldType.bit:
                    case FieldType.@float:
                        {
                            if (item.Value != null)
                                sbValue.Append(string.Format("  {0}={1},", item.Key, item.Value.ToString()));
                            else
                                sbValue.Append(string.Format(" {0}=null, ", item.Key));
                            break;
                        }
                    case FieldType.lookup:
                        {
                            if (item.Value != null && ((EntityReference)item.Value).id.ToString() != "")
                                sbValue.Append(string.Format(" {0}='{1}',", item.Key, ((EntityReference)item.Value).id.ToString()));
                            else
                                sbValue.Append(string.Format(" {0}=null, ", item.Key));
                            break;
                        }
                    case FieldType.password:
                        {
                            if (item.Value != null)
                                sbValue.Append(string.Format(" {0}='{1}',", item.Key, System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(item.Value.ToString(), "MD5")));
                            else
                                sbValue.Append(string.Format(" {0}=null, ", item.Key));
                            break;
                        }
                }

            }

            string commandText = string.Format(@"--更新记录
                                                UPDATE  {0} SET
                                                {1}
                                                WHERE {0}Id='{2}'", entityName, sbValue.ToString().TrimEnd(','), values[entityName + "id"].ToString());
            return commandText;
        }

        public static string ToDeleteCommand(string entityName, Guid id)
        {
            string commandText = string.Format(@"--删除记录
                                                DELETE  FROM {0}
                                                WHERE   {0}Id = '{1}'", entityName, id.ToString());
            return commandText;
        }

        public static string ToSoftDeleteCommand(string entityName, Guid id)
        {
            string commandText = string.Format(@"--软删除记录 设置DeletionStateCode为1
                                                update  {0} set
                                                DeletionStateCode = 1
                                                WHERE   {0}Id = '{1}'", entityName, id.ToString());
            return commandText;
        }

        public static string ToAssociateCommnad(string entityName, string relationName, Guid id, List<EntityReference> relatedEntities)
        {
            StringBuilder associateText = new StringBuilder();
            string CommandText = @"INSERT  INTO {0}
                                            ( {1}Id, {2}Id )
                                    VALUES  ( '{3}', '{4}' )
                                    ";
            foreach (EntityReference entityref in relatedEntities)
            {
                if (entityref.id != null)
                {
                    associateText.Append(string.Format(CommandText, relationName, entityName, entityref.entityname, id, entityref.id));
                }
            }

            return associateText.ToString();
        }

        public static string ToDisAssociateCommnad(string entityName, string relationName, Guid id, List<EntityReference> relatedEntities)
        {
            StringBuilder associateText = new StringBuilder();
            string CommandText = @"DELETE  FROM {0}
                                    WHERE   {1}Id = '{3}'
                                            AND {2}Id = '{4}'
                                    ";
            foreach (EntityReference entityref in relatedEntities)
            {
                if (entityref.id != null)
                {
                    associateText.Append(string.Format(CommandText, relationName, entityName, entityref.entityname, id, entityref.id));
                }
            }

            return associateText.ToString();
        }

        public static string[] ToRetrieveMultipleCommand(string entityName, string prefix, Dictionary<string, Field> fields, string[] columns, string order, int pageNum, int pageSize, string where, string rightRecord)
        {
            where = string.IsNullOrWhiteSpace(where) ? "" : string.Format(" WHERE {0} ", where);
            string[] commandTexts = new string[2];
            int startRow = pageNum * pageSize + 1, endRow = (pageNum + 1) * pageSize;
            string select = "t.*";
            if (columns != null)
                select = "t." + string.Join(",t.", columns);
            string AllSelect = "t.*";

            int i = 0;
            StringBuilder sbJoins = new StringBuilder();
            StringBuilder sbSelect = new StringBuilder();
            foreach (var item in fields)
            {
                if (item.Value.Type == FieldType.lookup)
                {
                    i++;
                    sbJoins.Append(string.Format(@"----关联实体引用
                                                LEFT JOIN {0} j{1} WITH(NOLOCK) ON t.{2}=j{1}.{0}Id ", item.Value.LookupEntityName,
                                                                                                  i.ToString(),
                                                                                                    item.Value.Name));
                    string lookupPrimaryAtt = "";//= "Name";
                    //if (prefix != null && item.Value.LookupEntityName.StartsWith(prefix))
                    //    lookupPrimaryAtt = string.Format("{0}_Name", prefix);
                    lookupPrimaryAtt = item.Value.LookupPrimaryAtt;
                    sbSelect.Append(string.Format(@",j{0}.{1} AS {2}Name ", i.ToString(), lookupPrimaryAtt, item.Value.Name));
                }
            }



            //select += sbSelect.ToString();

            AllSelect += sbSelect.ToString();
            string commandText = "";
            if (!string.IsNullOrEmpty(rightRecord))
            {
                commandText = string.Format(@"--查询记录
                                                {7}

                                              SELECT {0} FROM (
                                                    SELECT   ROW_NUMBER() OVER ( ORDER BY t.{4} ) AS rowId ,t.*
                                                     FROM    ( SELECT   
                                                                {8}
                                                               FROM    {1} t WITH(NOLOCK) 
                                                                {2}
                                                               INNER JOIN @T temp ON  temp.ID = t.{1}Id
                                                             ) AS t
													{3}
													) AS t
                                               WHERE   t.rowId BETWEEN {5} AND {6} ", select, entityName, sbJoins.ToString(), where, order, startRow, endRow, rightRecord, AllSelect);
            }
            else
            {
                commandText = string.Format(@"--查询记录
                                                SELECT {0} FROM (
                                                    SELECT   ROW_NUMBER() OVER ( ORDER BY t.{4} ) AS rowId ,t.*
                                                     FROM    ( SELECT   
                                                               {8}
                                                               FROM    {1} t WITH(NOLOCK) 
                                                                {2}
                                                             ) AS t
													{3}
													) AS t
                                               WHERE   t.rowId BETWEEN {5} AND {6}  ", select, entityName, sbJoins.ToString(), where, order, startRow, endRow, rightRecord, AllSelect);
            }
            commandTexts[0] = commandText;

            if (!string.IsNullOrEmpty(rightRecord))
            {
                commandText = string.Format(@"--查询记录
                                        {4}
                                        select COUNT(1) AS total from (
                                        SELECT    {5}
                                        FROM    {1} t WITH(NOLOCK) 
                                        {2}
                                        INNER JOIN @T temp ON  temp.ID = t.{1}Id
                                        )t
                                        {3}                                                  
                                        ", select, entityName, sbJoins.ToString(), where, rightRecord, AllSelect);
            }
            else
            {
                commandText = string.Format(@"--查询记录
                                     
                                        select COUNT(1) AS total from (
                                        SELECT   {4}
                                        FROM    {1} t WITH(NOLOCK) 
                                        {2}
                                       
                                        )t
                                        {3}                                                  
                                        ", select, entityName, sbJoins.ToString(), where, AllSelect);

            }
            commandTexts[1] = commandText;

            return commandTexts;
        }



        /// <summary>
        /// 获取多对多列表SQL语句
        /// </summary>
        /// <param name="entityName"></param>
        /// <param name="prefix"></param>
        /// <param name="fields"></param>
        /// <param name="columns"></param>
        /// <param name="order"></param>
        /// <param name="pageNum"></param>
        /// <param name="pageSize"></param>
        /// <param name="where"></param>
        /// <param name="rightRecord"></param>
        /// <returns></returns>
        public static string[] ToRetrieveMultipleN2NCommand(string entityName, string EntityM, string prefix, Dictionary<string, Field> fields, string[] columns, string order, int pageNum, int pageSize, string where)
        {
            string[] commandTexts = new string[2];
            int startRow = pageNum * pageSize + 1, endRow = (pageNum + 1) * pageSize;
            string select = "t.*";
            if (columns != null)
                select = "t." + string.Join(",t.", columns);
            string AllSelect = "t.*";

            int i = 0;
            StringBuilder sbJoins = new StringBuilder();
            StringBuilder sbSelect = new StringBuilder();
            foreach (var item in fields)
            {
                if (item.Value.Type == FieldType.lookup)
                {
                    i++;
                    sbJoins.Append(string.Format(@"----关联实体引用
                                                LEFT JOIN {0} j{1} WITH(NOLOCK) ON t.{2}=j{1}.{0}Id ", item.Value.LookupEntityName,
                                                                                                  i.ToString(),
                                                                                                    item.Value.Name));
                    string lookupPrimaryAtt = "";
                    lookupPrimaryAtt = item.Value.LookupPrimaryAtt;
                    sbSelect.Append(string.Format(@",j{0}.{1} AS {2}Name ", i.ToString(), lookupPrimaryAtt, item.Value.Name));
                }
            }

            AllSelect += sbSelect.ToString();

            if (string.IsNullOrEmpty(order))
            {
                order = "CreatedOn";
            }

            commandTexts[0] = string.Format(@"--查询记录
                                                SELECT {0} 
                                                FROM (
                                                        SELECT   ROW_NUMBER() OVER ( ORDER BY t.{5} ) AS rowId ,t.*
                                                        FROM(SELECT {8}
                                                             FROM {1} t WITH(NOLOCK) 
                                                             {3}
                                                            ) AS t
                                                        inner join {2} as m with(nolock) on m.{1}Id=t.{1}Id
                                                        {4}
													) AS t
                                               WHERE t.rowId BETWEEN {6} AND {7}  ",
                                                                                   select,                  //0
                                                                                   entityName,              //1
                                                                                   EntityM,                 //2
                                                                                   sbJoins.ToString(),      //3
                                                                                   where,                   //4
                                                                                   order,                   //5
                                                                                   startRow,                //6
                                                                                   endRow,                  //7
                                                                                   AllSelect);              //8



            commandTexts[1] = string.Format(@"--查询记录数
                                        SELECT COUNT(1) AS total from(
                                        SELECT {4}
                                        FROM {0} t WITH(NOLOCK) 
                                        {2})t
                                        inner join {1} as m with(nolock) on m.{0}Id=t.{0}Id
                                        {3}",
                                            entityName,
                                            EntityM,
                                            sbJoins.ToString(),
                                            where,
                                            AllSelect);

            return commandTexts;
        }

    }


    #endregion
}
